/*-----------------------------------------------------------------------------------
 *
 * 프로그램명	: HomePageDAO.java
 * Version		: 1.0
 * 작성일 		: 2006-08-16
 * 작성자		:
 * 수정일 		:
 * 수정자		:
 * 설명		 	:
 * 기능		 	:
 *
 * Copyright (c) 2000 by H&H  All Rights Reserved.
 *
-------------------------------------------------------------------------------------*/
package com.joinhawaii.admin.sales;

import java.io.*;

import java.util.*;
import java.sql.*;

import com.zll.framework.db.DBManager;
import com.zll.framework.model.HashBox;
import com.zll.framework.model.HashList;
import com.zll.framework.util.StringUtil;



public class SalesDAO
{
	public HashList selectReqList(HashMap infos) throws Exception
	{
		if (infos.get("curr_page") != null) {
			StringUtil.setPaging("/sales.xml", "select_req_total", infos);
		}
        return DBManager.executeQuery("/sales.xml", "select_req_list", infos);
	}

	public HashList selectReqInfo(HashMap infos) throws Exception
	{
		return DBManager.executeQuery("/sales.xml", "select_req_info", infos);
	}

	public HashList selectReqHotelList(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/sales.xml", "select_req_hotel_list", infos);
	}

	public HashList selectReqCustomerList(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/sales.xml", "select_req_customer_list", infos);
	}

	public HashList selectReqEnjoyList(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/sales.xml", "select_req_enjoy_list", infos);
	}

	public HashList selectReqCarList(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/sales.xml", "select_req_car_list", infos);
	}

	public HashList selectEstList(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/sales.xml", "select_est_list", infos);
	}

	public HashList selectEstInfo(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/sales.xml", "select_est_info", infos);
	}

	public HashList selectConfirmList(HashMap infos) throws Exception
	{
        return DBManager.executeQuery("/sales.xml", "select_confirm_list", infos);
	}

	public HashList selectGuideList(HashMap infos) throws Exception
	{
		return DBManager.executeQuery("/sales.xml", "select_guide_list", infos);
	}

	public HashList selectCustomerList(HashMap infos) throws Exception {
        return DBManager.executeQuery("/sales.xml", "select_customer_list", infos);
	}

	public HashList selectAirList(HashMap infos) throws Exception {
        return DBManager.executeQuery("/sales.xml", "select_air_list", infos);
	}

	public HashList selectFlyList(HashMap infos) throws Exception {
        return DBManager.executeQuery("/sales.xml", "select_fly_list", infos);
	}

	public HashList selectHotelList(HashMap infos) throws Exception {
        return DBManager.executeQuery("/sales.xml", "select_hotel_list", infos);
	}

	public HashList selectEnjoyList(HashMap infos) throws Exception {
        return DBManager.executeQuery("/sales.xml", "select_enjoy_list", infos);
	}

	public HashList selectCarList(HashMap infos) throws Exception {
        return DBManager.executeQuery("/sales.xml", "select_car_list", infos);
	}

	public HashList selectInsureList(HashMap infos) throws Exception {
        return DBManager.executeQuery("/sales.xml", "select_insure_list", infos);
	}



	/* 매출리스트 */
	public HashList selectSalesList(HashMap infos) throws Exception {
        return DBManager.executeQuery("/sales.xml", "select_sales_list", infos);
	}

	/* 정산리스트 */
	public HashList selectAccountList(HashMap infos) throws Exception {
        return DBManager.executeQuery("/sales.xml", "select_account_list", infos);
	}


	 /**
	 * 견적의뢰 저장
	 *
	 * @param	infos
	 * @return	boolean
	 */
	public String saveReq(HashBox box) throws Exception
    {
		Connection conn = null;

		String reqId = "";

        try
		{
			conn = DBManager.getConnection();
			HashMap map = box.getParam();

	    	if ("C".equals(map.get("job_type"))) {
	    		reqId = DBManager.executeQuery("/sales.xml", "select_req_id", map, conn).get("req_id", 0);
	    		map.put("req_id", reqId);
	    		DBManager.executeUpdate("/sales.xml", "insert_req", map, conn);
	       	} else if ("U".equals(map.get("job_type")))	{
	       		DBManager.executeUpdate("/sales.xml", "update_req", map, conn);
	       	} else if ("D".equals(map.get("job_type"))) {
	       		DBManager.executeUpdate("/sales.xml", "delete_req", map, conn);
	        }

			conn.commit();

			return reqId;
	    }
	    catch(Exception e)
	    {
	    	DBManager.rollback(conn);
	    	throw e;
		}
	    finally
	    {
	    	DBManager.closeConnection(conn);
		}
    }







	/**
	 * 견적서 등록
	 *
	 * @param	infos
	 * @return	boolean
	 */
	public String insertEst(HashBox box) throws Exception
    {
		Connection conn = null;
        //boolean result	= true;
		String estNo = "";

        try
		{
			conn = DBManager.getConnection();
			HashMap map = box.getParam();

			//견적서번호 채번
			estNo = DBManager.executeQuery("/sales.xml", "select_est_no", map, conn).get("est_no", 0);


			//고객명
			String customerName = "";
			HashList customer 	= box.getList("customer");
			for (int i = 0; i < customer.size(); i++) {
				if (!"".equals(customer.get("customer_name",i))) {
					customerName += customer.get("customer_name",i) + ",";
				}
			}


			//견적서 또는 예약확인서
			map.put("customer_name", customerName.substring(0, customerName.length()-1));
			map.put("est_no", estNo);
			map.put("parent_est_no", estNo);
			DBManager.executeUpdate("/sales.xml", "insert_est_master", map, conn);

			//상품상세입력
			insertEstList(conn, box, estNo);

			//예약확인서인 경우, 안내문 입력
			if ("2".equals(map.get("est_type"))) {
				DBManager.executeUpdate("/sales.xml", "insert_est_guide", map, conn);
			}



			conn.commit();
	    }
	    catch(Exception e)
	    {
	    	DBManager.rollback(conn);
	    	throw e;
		}
	    finally
	    {
	    	DBManager.closeConnection(conn);
		}

        return estNo;
    }

	/**
	 * 견적서 수정
	 *
	 * @param	infos
	 * @return	boolean
	 */
	public void updateEst(HashBox box) throws Exception
    {
		Connection conn = null;
        //boolean result	= true;

        try
		{
			conn = DBManager.getConnection();
			HashMap map = box.getParam();

			String estNo	= StringUtil.nvl(map.get("est_no"));

			//고객명
			String customerName = "";
			HashList customer 	= box.getList("customer");
			for (int i = 0; i < customer.size(); i++) {
				if (!"".equals(customer.get("customer_name",i))) {
					customerName += customer.get("customer_name",i) + ",";
				}
			}

			//견적서
			map.put("customer_name", customerName.substring(0, customerName.length()-1));
			DBManager.executeUpdate("/sales.xml", "update_est_master", map, conn);

			//삭제ALL
			DBManager.executeUpdate("/sales.xml", "delete_est_customer_all", map, conn);
			DBManager.executeUpdate("/sales.xml", "delete_est_air_all", map, conn);
			DBManager.executeUpdate("/sales.xml", "delete_est_fly_all", map, conn);
			DBManager.executeUpdate("/sales.xml", "delete_est_hotel_all", map, conn);
			DBManager.executeUpdate("/sales.xml", "delete_est_enjoy_all", map, conn);
			DBManager.executeUpdate("/sales.xml", "delete_est_car_all", map, conn);
			DBManager.executeUpdate("/sales.xml", "delete_est_insure_all", map, conn);

			//상품입력
			insertEstList(conn, box, estNo);

			conn.commit();
	    }
	    catch(Exception e)
	    {
	    	DBManager.rollback(conn);
	    	throw e;
		}
	    finally
	    {
	    	DBManager.closeConnection(conn);
		}
    }


	/**
	 * 견적서 상태변경
	 *
	 * @param	infos
	 * @return	boolean
	 */
	public void updateEstState(HashBox box) throws Exception
    {
		Connection conn = null;
        //boolean result	= true;

        try
		{
			conn = DBManager.getConnection();
			HashMap map = box.getParam();

			//견적서
			DBManager.executeUpdate("/sales.xml", "update_est_state", map, conn);

			conn.commit();
	    }
	    catch(Exception e)
	    {
	    	DBManager.rollback(conn);
	    	throw e;
		}
	    finally
	    {
	    	DBManager.closeConnection(conn);
		}
    }

	/**
	 * 견적서 삭제
	 *
	 * @param	infos
	 * @return	boolean
	 */
	public void deleteEst(HashBox box) throws Exception
    {
		Connection conn = null;
        //boolean result	= true;

        try
		{
			conn = DBManager.getConnection();
			HashMap map = box.getParam();

			DBManager.executeUpdate("/sales.xml", "delete_est_master", map, conn);
			DBManager.executeUpdate("/sales.xml", "delete_est_customer_all", map, conn);
			DBManager.executeUpdate("/sales.xml", "delete_est_air_all", map, conn);
			DBManager.executeUpdate("/sales.xml", "delete_est_fly_all", map, conn);
			DBManager.executeUpdate("/sales.xml", "delete_est_hotel_all", map, conn);
			DBManager.executeUpdate("/sales.xml", "delete_est_enjoy_all", map, conn);
			DBManager.executeUpdate("/sales.xml", "delete_est_car_all", map, conn);
			DBManager.executeUpdate("/sales.xml", "delete_est_insure_all", map, conn);

			conn.commit();
	    }
	    catch(Exception e)
	    {
	    	DBManager.rollback(conn);
	    	throw e;
		}
	    finally
	    {
	    	DBManager.closeConnection(conn);
		}
    }


	public void updateEstIssue(HashBox box) throws Exception
    {
		Connection conn = null;
        //boolean result	= true;

        try
		{
			conn = DBManager.getConnection();

			HashList list 	= box.getList("est");
			for (int i = 0; i < list.size(); i++) {
				HashMap infos = (HashMap)list.get(i);
				if ("Y".equals(list.get("chk", i))) {
					infos.put("est_state", box.get("est_state"));
					if ("master".equals(list.get("gubn",i))) {
						DBManager.executeUpdate("/sales.xml", "issue_est_master", (HashMap)list.get(i), conn);
					} else {
						DBManager.executeUpdate("/sales.xml", "issue_est", (HashMap)list.get(i), conn);
					}
				}
			}


			conn.commit();
	    }
	    catch(Exception e)
	    {
	    	DBManager.rollback(conn);
	    	throw e;
		}
	    finally
	    {
	    	DBManager.closeConnection(conn);
		}
    }



	public void updateSalesList(HashBox box) throws Exception
    {
		Connection conn = null;
        //boolean result	= true;

        try
		{
			conn = DBManager.getConnection();

			HashList list 	= box.getList("list");
			for (int i = 0; i < list.size(); i++) {
				DBManager.executeUpdate("/sales.xml", "update_est_cost", (HashMap)list.get(i), conn);
			}


			conn.commit();
	    }
	    catch(Exception e)
	    {
	    	DBManager.rollback(conn);
	    	throw e;
		}
	    finally
	    {
	    	DBManager.closeConnection(conn);
		}
    }



	/**
	 * 바우처 저장
	 *
	 * @param	infos
	 * @return	boolean
	 */
	public void updateVoucher(HashBox box) throws Exception
    {
		Connection conn = null;

        try
		{
			conn = DBManager.getConnection();
			HashMap map = box.getParam();

	    	DBManager.executeUpdate("/sales.xml", "update_voucher_"+box.get("voucher_gubn"), map, conn);

	    	if ("fly".equals(box.get("voucher_gubn"))) {
	    		DBManager.executeUpdate("/sales.xml", "delete_fly_seat", map, conn);
	    		HashList list = box.getList("seat");
	    		for (int i = 0; i < list.size(); i++) {
		    		DBManager.executeUpdate("/sales.xml", "insert_fly_seat", (HashMap)list.get(i), conn);
	    		}
	    	}
			conn.commit();

	    }
	    catch(Exception e)
	    {
	    	DBManager.rollback(conn);
	    	throw e;
		}
	    finally
	    {
	    	DBManager.closeConnection(conn);
		}
    }

	private void insertEstList(Connection conn, HashBox box, String estNo) throws Exception
	{
		//고객기본정보
		HashList customer 	= box.getList("customer");
		for (int i = 0; i < customer.size(); i++) {
			HashMap infos = (HashMap)customer.get(i);
			infos.put("est_no", estNo);
			infos.put("est_seq", String.valueOf(i+1));
			if (!"".equals(infos.get("customer_name"))) {
				DBManager.executeUpdate("/sales.xml", "insert_est_customer", infos, conn);
			}
		}

		//국제선
		HashList air 	= box.getList("air");
		for (int i = 0; i < air.size(); i++) {
			HashMap infos = (HashMap)air.get(i);
			infos.put("est_no", estNo);
			infos.put("est_seq", String.valueOf(i+1));
			if (!"".equals(infos.get("air_code"))) {
				DBManager.executeUpdate("/sales.xml", "insert_est_air", infos, conn);
			}
		}

		//주내선
		HashList fly 	= box.getList("fly");
		for (int i = 0; i < fly.size(); i++) {
			HashMap infos = (HashMap)fly.get(i);
			infos.put("est_no", estNo);
			infos.put("est_seq", String.valueOf(i+1));
			if (!"".equals(infos.get("air_code"))) {
				DBManager.executeUpdate("/sales.xml", "insert_est_fly", infos, conn);
			}
		}

		//호텔
		HashList hotel 	= box.getList("hotel");
		for (int i = 0; i < hotel.size(); i++) {
			HashMap infos = (HashMap)hotel.get(i);
			infos.put("est_no", estNo);
			infos.put("est_seq", String.valueOf(i+1));
			if (!"".equals(infos.get("land_code"))) {
				DBManager.executeUpdate("/sales.xml", "insert_est_hotel", infos, conn);
			}
		}

		//선택관광
		HashList enjoy 	= box.getList("enjoy");
		for (int i = 0; i < enjoy.size(); i++) {
			HashMap infos = (HashMap)enjoy.get(i);
			infos.put("est_no", estNo);
			infos.put("est_seq", String.valueOf(i+1));
			if (!"".equals(infos.get("land_code"))) {
				DBManager.executeUpdate("/sales.xml", "insert_est_enjoy", infos, conn);
			}
		}

		//렌터카
		HashList car 	= box.getList("car");
		for (int i = 0; i < car.size(); i++) {
			HashMap infos = (HashMap)car.get(i);
			infos.put("est_no", estNo);
			infos.put("est_seq", String.valueOf(i+1));
			if (!"".equals(infos.get("land_code"))) {
				DBManager.executeUpdate("/sales.xml", "insert_est_car", infos, conn);
			}
		}

		//보험
		HashList insure 	= box.getList("insure");
		for (int i = 0; i < insure.size(); i++) {
			HashMap infos = (HashMap)insure.get(i);
			infos.put("est_no", estNo);
			infos.put("est_seq", String.valueOf(i+1));
			if (!"".equals(infos.get("insure_name"))) {
				DBManager.executeUpdate("/sales.xml", "insert_est_insure", infos, conn);
			}
		}
	}


}
